-- **************************************************************************
--    Project Name:   中转-出港转运及时率中心下一站汇总表
--    Job Name:       jms_dm.dm_cn_departure_transfer_timely_route_analysis_dt
--    Author :        季修魁
--    date：          2023/08/30
-- **************************************************************************
-- **************************************************************************

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions=1000;
set hive.exec.max.dynamic.partitions.pernode=1000;

insert overwrite table jms_dm.dm_cn_departure_transfer_timely_route_analysis_dt partition (dt)
select start_code,                                                                                                                                                                --始发地 发件中心
       max(start_name)                                                                                                                             as start_name,                       --始发地 发件中心
       max(order_source_code)                                                                                                                      as order_source_code,                --订单来源:1.桃花岛，2.抖音-紫金山，3.快手-逍遥峰，4.其他
       order_source_name,                                                                                                                                                               --订单来源
       center_type,                                                                                                                                                                     --中心类型,1始发2中转
       max(start_agent_code)                                                                                                                       as start_agent_code,                 --发件代理区
       max(start_agent_name)                                                                                                                       as start_agent_name,                 --发件代理区
       sum(1)                                                                                                                                      as send_account,                     --发件票数
       sum(if(untimely_cause = 1, 1, 0))                                                                                                           as route_lack_account,               --静态路由缺失票数
       sum(if(untimely_cause = 8, 1, 0))                                                                                                           as transfer_intime_account,          --转运及时票数
       sum(if(untimely_cause = 8, 1, 0)) / (sum(1) - sum(if(untimely_cause = 1, 1, 0))-sum(if(untimely_cause = 11, 1, 0))-sum(if(untimely_cause = 2, 1, 0)))                         as transfer_intime_rate,             --出港转运及时率
       sum(if(untimely_cause = 7, 1, 0))                                                                                                           as without_arrival_account,          --无到达时间票数
       sum(if(untimely_cause = 2, 1, 0))                                                                                                           as error_send_account,               --错发票数
       sum(if(untimely_cause = 5, 1, 0))                                                                                                           as opp_delay_account,                --操作延误票数
       sum(if(untimely_cause = 4, 1, 0))                                                                                                           as send_delay_account,               --发车晚点票数
       sum(if(untimely_cause = 3, 1, 0))                                                                                                           as shipment_delay_account,           --任务单问题票数
       sum(if(untimely_cause = 6, 1, 0))                                                                                                           as stop_delay_account,               --经停点问题票数
       sum(if(all_line_is_timely = 1, 1, 0))                                                                                                       as all_line_transfer_intime_account, --全链路-转运及时票数
       sum(if(all_line_is_timely = 1, 1, 0)) / (sum(1) - sum(if(all_line_untimely_cause = 1, 1, 0)) - sum(if(all_line_untimely_cause = 11, 1, 0))) as all_line_transfer_intime_rate,    --全链路-转运及时率
       sum(if(untimely_cause = 9, 1, 0))                                                                                                           as shipment_error_account,           --任务单错用票数
       mage_region_code,
       max(mage_region_name)                                                                                                                       as mage_region_name,
       sum(if(untimely_cause = 11, 1, 0))                                                                                                          as temporary_dispatch_account,
       sum(if(untimely_cause = 12, 1, 0))                                                                                                          as reserve_route_account,
       sum(if(untimely_cause = 13, 1, 0))                                                                                                          as epidemic_hoarding_account,
       sum(is_store)                                                                                                                               as store_count,
       express_type_code,
       express_type_name,
       sum(if(reserved_seconds='-',0,reserved_seconds)) as reserved_seconds,
       start_provider_id,
       start_provider_desc,
       last_send_shipment_name,
       send_shipment_name,
       nextstationcode,
       nextstation,
       net1.network_type as nextstation_network_type,
       end_center_code,
       end_center_name,
       dt as date_time,
       last_network_code,
       last_network_name,
       dt
from jms_dm.dm_cn_departure_transfer_timely_route_dt t1
left join jms_dim.dim_network_whole_massage net1 on t1.nextstationcode=net1.code
where dt>=date_add('{{ execution_date | cst_ds }}', -2) and dt<=date_add('{{ execution_date | cst_ds }}', -1)
group by start_code, order_source_name, dt, center_type, mage_region_code,express_type_code,express_type_name,start_provider_id,start_provider_desc,
       last_send_shipment_name,
       send_shipment_name,
       nextstationcode,
       nextstation,
       end_center_code,
       end_center_name,
       net1.network_type,
       last_network_code,
       last_network_name
distribute by dt,pmod(hash(rand()), 5)
;